const query = require('./query');


const getByPage = function (tb, page, limit) {
    return new Promise((resolve, reject) => {
        let start = (page - 1) * limit;
        let command = `select * from ${tb} limit ${start},${limit}`;
        query(command, function (res) {
            let data = {
                code: 200,
                message: '获取成功',
                data: {
                    list: res,
                    pagination: {
                        size: res.length,
                        currentPage: parseInt(page)
                    }
                }
            };
            query(`select count(*) from ${tb}`, function (res) {
                data.data.pagination['total'] = res[0]["count(*)"];
                data.data.pagination['totalPage'] = parseInt(res[0]["count(*)"] / limit) + ((res[0]["count(*)"] % limit) > 0 ? 1 : 0);
                resolve(data);
            }, function (err) {
                resolve(data)
            })
        }, function (err) {
            resolve(err);
        })
    })
};
const getForeignInfo = function (tb, filter, foreign) {//主表，筛选条件,外键信息
    let queryStr = '';//查询条件
    for (let key in filter) {
        queryStr += `${tb}.${key}=${filter[key]}&`;
    }
    queryStr = queryStr.substr(0, queryStr.length - 1);
    let as = '';
    let join = '';
    let tables = ` from ${tb} ${tb}`;
    for (let key1 in foreign) {
        let table = foreign[key1].table;
        let data = foreign[key1].data;
        let key = key1;
        join += ` join ${table} ${table} on ${tb}.${key}=${table}.id `;
        for (let key2 in data) {
            as += `,${table}.${key2} as ${data[key2]}`
        }
    }
    let str = `select ${tb}.*` + as + tables + join + (queryStr == '' ? '' : 'where ' + queryStr);
    return str;
};
const Sql = {
    queryAll: function (tb, filter, foreign) {  //获取表的全部记录
        if (filter && !Tools.isEmptyObject(filter)) { //分页
            return getByPage(tb, filter.page, filter.limit, foreign)
        } else {  //全部
            return new Promise((resolve, reject) => {
                let str = `select * from ${tb}`;
                if (foreign) {
                    str = getForeignInfo(tb, filter, foreign);
                }
                query(str, function (res) {
                    let data = {
                        code: 200,
                        message: '获取成功',
                        data: {
                            list: res,
                            size: res.length
                        }
                    };
                    resolve(data);
                }, function (err) {
                    resolve(err);
                })
            })
        }
    },
    insert: function (tb, data) {  //插入一条记录  data    {xx:"xx"}
        return new Promise((resolve, reject) => {
            let [keys, values] = [[], []];
            let str;
            for (let key in data) {
                if (data.hasOwnProperty(key)) {
                    keys.push(key);
                    if (Object.prototype.toString.call(data[key]) == '[object String]') {
                        values.push(`"${data[key]}"`)
                    } else {
                        values.push(data[key])
                    }
                }
            }
            str = `insert into ${tb} (${keys}) values (${values})`;

            query(str, function (res) {
                let id = res.insertId;
                let data = {
                    code: 200,
                    message: '添加成功',
                    data: res
                };
                query(`select * from ${tb} where id=${id}`, function (res) {
                    data.data = res[0];
                    resolve(data);
                }, function (err) {
                    resolve(data);
                })
            }, function (err) {
                resolve(err);
            })
        })
    },
    insertRows: function (tb, arr) { //插入多条记录  tb:表名 arr数组[{xx:"xx",xx:"xx"},{:},{:}] xx:键值对数据
        return new Promise((resolve, reject) => {
            let [keys, values] = [[], []];
            for (let i = 0; i < arr.length; i++) {
                let [data, value] = [arr[i], []];
                for (let key in data) {
                    if (data.hasOwnProperty(key)) {
                        if (i == 0) {
                            keys.push(key);
                        }
                        if (Object.prototype.toString.call(data[key]) == '[object String]') {
                            value.push(`"${data[key]}"`)
                        } else {
                            value.push(data[key])
                        }
                    }
                }
                values.push(`(${value})`);
            }
            query(`insert into ${tb} (${keys}) values ${values}`, function (res) {
                let data = {
                    code: 200,
                    message: '添加成功',
                    data: res
                };
                let ids = [];
                for (let i = 0; i < res.affectedRows; i++) {
                    ids.push(res.insertId + i);
                }
                query(`select * from ${tb} where id in (${ids})`, function (res) {
                    data.data = {
                        list: res,
                        size: res.length
                    };
                    resolve(data);
                }, function (err) {
                    resolve(data);
                })
            }, function (err) {
                resolve(err);
            })
        })
    },
    update: function (tb, id, data) { //根据id修改单条记录  data:{xx:"xx"}
        return new Promise((resolve, reject) => {
            let [str, index] = ['', 0];
            for (let key in data) {
                if (data.hasOwnProperty(key)) {
                    if (index != 0) {
                        str += ','
                    }
                    if (Object.prototype.toString.call(data[key]) == '[object String]') {
                        str += `${key}="${data[key]}"`
                    } else {
                        str += `${key}=${data[key]}`
                    }
                    index++;
                }
            }
            query(`update ${tb} set ${str} where id=${id}`, function (res) {
                let data = {
                    code: 200,
                    message: '修改成功',
                    data: res
                };
                query(`select * from ${tb} where id=${id}`, function (res) {
                    data.data = res[0];
                    if (res[0] === undefined) {
                        resolve({"code": 204, "msg": "违规id"})
                    } else {
                        resolve(data);
                    }
                }, function (err) {
                    resolve(err);
                })
            }, function (err) {
                resolve(err);
            })
        })
    },
    updateRows: function (tb, arr) {  //修改多条记录[{id:"xx",xx:""}]
        return new Promise((resolve, reject) => {
            let [str, ids, len, keys] = ['', [], arr.length, Object.keys(arr[0])];
            for (let x = 0; x < len; x++) {
                ids.push(arr[x].id);
            }
            for (let i = 0; i < keys.length; i++) {
                let k = keys[i];
                if (k != 'id') {
                    str += `${k} = case id `;
                    for (let j = 0; j < len; j++) {
                        str += `when ${arr[j].id} then `;
                        if (Object.prototype.toString.call(arr[j][k]) == '[object String]') {
                            str += `"${arr[j][k]}" `
                        } else {
                            str += `${arr[j][k]} `
                        }
                    }
                    str += 'end';
                    if (i < keys.length - 1) {
                        str += ','
                    }
                }
            }
            query(`update ${tb} set ${str} where id in (${ids})`, function (res) {
                let data = {
                    code: 200,
                    message: '修改成功',
                    data: res
                };
                query(`select * from ${tb} where id in (${ids})`, function (res) {
                    data.data = {
                        list: res,
                        size: res.length
                    };
                    resolve(data);
                }, function (err) {
                    resolve(data);
                })
            }, function (err) {
                resolve(err);
            })
        })
    },
    delete: function (tb, id) { //根据id删除单条记录
        return new Promise((resolve, reject) => {
            query(`delete from ${tb} where id=${id}`, function (res) {
                let data = {
                    code: 200,
                    message: '删除成功',
                };
                if (res.affectedRows === 1) {
                    resolve(data);
                } else {
                    resolve({code: 202, "msg": "删除的数据未找到~"})
                }
            }, function (err) {
                resolve(err);
            })
        })
    },
    deleteRows: function (tb, data) { //根据id数组删除多条记录
        return new Promise((resolve, reject) => {
            query(`delete from ${tb} where id in (${data})`, function (res) {
                let data = {
                    code: 200,
                    message: '删除成功',
                };
                if (res.affectedRows === 0) {
                    resolve({code: 202, message: "删除的数据未找到"})
                } else {
                    resolve(data);
                }

            }, function (err) {
                resolve(err);
            })
        })
    },
    oneTabSearch: function (tb, fields, filters,) {
        //单表查询,查询条件,查询条件传值或者不传值进行查询
        //tb:表名 fields:查询字段 []列表 filters:查询条件 {}数组
        //根据条件准确查询所有或者某些字段的一条数据  filters:数组 {"xxx":"xxx","xx":"xx"} fields: ["xxx","xx"] 需要查询的字段
        let queryStr = '';//查询条件
        let fieldStr = '';//查询字段
        if (fields) {
            for (let i = 0; i < fields.length; i++) {
                fieldStr += `${fields[i]},`;
            }
            fieldStr = fieldStr.substr(0, fieldStr.length - 1);
        } else {
            fieldStr = '*'
        }

        let str;
        if (filters) {
            for (let key in filters) {
                queryStr += `${key}="${filters[key]}" and `;
            }
            queryStr = queryStr.substr(0, queryStr.length - 5);
            str = `select ${fieldStr} from ${tb} where ${queryStr}`
        } else {
            str = `select ${fieldStr} from ${tb}`
        }

        return new Promise((resolve, reject) => {
            query(str, function (res) {
                let data = {
                    code: res.length === 0 ? 205 : 200,
                    message: res.length === 0 ? '获取失败!' : '获取成功',
                    data: res.length === 0 ? {} : res
                };
                return resolve(data);
            }, function (err) {
                return false;
            });
        });
    },
    twoTabSearch: function (tb1, tb2, fields, filter) {
        //两表查询 查询条件,查询条件传值或者不传值进行查询
        //tb:表名 fields:查询字段 []列表 filter:[]列表 查询条件 某个指定字段例如 id
        //根据条件准确查询所有或者某些字段的一条数据
        let queryStr;//查询条件
        let fieldStr = '';//查询字段
        if (fields) {
            for (let i = 0; i < fields.length; i++) {
                fieldStr += `${fields[i]},`;
            }
            fieldStr = fieldStr.substr(0, fieldStr.length - 1);
        } else {
            fieldStr = '*'
        }
        queryStr = `${tb1}.${filter[0]}=${tb2}.${filter[1]}`;
        let str;
        str = `select ${fieldStr} from ${tb1} inner join ${tb2} on ${queryStr}`;
        // handlinfo(str);
        return new Promise((resolve, reject) => {
            query(str, function (res) {
                let data = {
                    code: res.length == 0 ? 205 : 200,
                    message: res.length == 0 ? '获取失败!' : '获取成功',
                    data: res.length == 0 ? {} : res
                };
                return resolve(data);
            }, function (err) {
                return false;
            });
        });

    },
    searchVague: function (tb, val, fields, foreign) {//根据条件模糊查询
        let str = `select * from ${tb} where concat(`;
        for (let i = 0; i < fields.length; i++) {
            str += `${fields[i]},`;
        }
        str = str.substring(0, str.length - 1);
        str += `) like %${val}%`;
        if (fields.length == 1) {
            str = `select * from ${tb} where ${fields[0]} like '%${val}%'`;
        }
        return new Promise((resolve, reject) => {
            query(str, function (res) {
                resolve({
                    code: 200,
                    message: '获取成功',
                    data: res
                });
            }, function (err) {
                resolve(err);
            });
        })
    },

    dataCount: function (tb, filters) {
        let queryStr = "";
        let str;

        for (let key in filters) {
            queryStr += `${key}="${filters[key]}"`;
        }
        str = `select 1 from ${tb} where ${queryStr} limit 1;`;
        return new Promise((resolve, reject) => {
            query(str, function (res) {
                let data = {
                    code: res.length == 0 ? 205 : 200,
                };
                return resolve(data);
            }, function (err) {
                return false;
            })
        })
    }
};

module.exports = Sql;
